
package vn.aptech.project.model.dao;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import vn.aptech.project.model.dto.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class  category_DAO extends CommonDAO {
 public  category_DAO() throws ClassNotFoundException, SQLException, IOException{
 }


public Vector selectall() throws SQLException
{
String strSelect = "SELECT * FROM FS_category";
return ExecSelect(strSelect);
}
public Vector topcategory() throws SQLException
{
String strSelect = "select Top 1 FS_category.typeinfo from FS_category inner join FS_customer on FS_category.Typeid=FS_customer.Typeid inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_orderline on FS_order.orderid=FS_orderline.orderid group by FS_category.typeinfo order by sum(FS_orderline.quantity) DESC";
return ExecSelect(strSelect);
}
public Vector selectTypeinfo() throws SQLException
{
String strSelect = "SELECT typeinfo FROM FS_category";
return ExecSelect(strSelect);
}
public int getCategoryIdByCategoryName(String typeinfo) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select Typeid from FS_category where typeinfo='"+typeinfo+"'";
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("Typeid"));
            int a =Integer.parseInt(String.valueOf(vt.get(0)));
            return a;

}
public String getCategoryNameByCategoryId(int typeid) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select typeinfo from FS_category where Typeid="+typeid;
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("typeinfo"));
            String a =String.valueOf(vt.get(0));
            return a;

}
public int insert(category_DTO  obj) throws SQLException
{
String strInsert = "INSERT INTO FS_category VALUES (N'"+  obj.gettypeinfo() +"' )";
return  ExecNonQuery(strInsert);
}


public int update(category_DTO  obj) throws SQLException
{
String strUpdate = "UPDATE FS_category SET typeinfo=N'"+ obj.gettypeinfo()+"' WHERE Typeid="+ obj.getTypeid() ;
return  ExecNonQuery(strUpdate);
}


public int delete(int Typeid) throws SQLException
{
String strDelete = "DELETE FROM FS_category WHERE Typeid="+Typeid;
return  ExecNonQuery(strDelete);
}
}

